<?php
/**
 * Description of Emms_Report_PortfolioAtRiskByBranch
 *
 * @author andres
 */
class Emms_Report_PortfolioAtRiskByBranch extends HTech_Report
{
    private $_par;
    
    public function __construct() {
        $this->_par = explode(',',Zend_Registry::get('HT_Config')->param->par_boundaries); 
        parent::__construct(); 
    }
    
    protected function _setName()
    {
        $this->_name = $this->translate('Portfolio at risk consolidated by branch');
    }
    
    protected function _setForm()
    {
        $this->_form = new HTech_Report_Form_Date();
    }
    
    public function _setIndex() 
    {
        $this->_index = 'LocationId';
    }
    
    protected function _setSql()
    {      
        foreach($this->_par as $delay)
            $this->_sql[] = sprintf('
                select 
                    lm.LocationId,
                    count(a.LoanId) Par%sLoans,
                    sum(a.BalancePrincipal) Par%sMnt
                from
                    arrears a
                left join
                    loans l on l.LoanId = a.LoanId
                left join
                    loan_masters lm on lm.LoanMasterId = l.LoanMasterId
                where
                    a.Date = :date and a.Delay >= %s
                group by
                    lm.LocationId', $delay, $delay, $delay);
        
        $this->_sql[] = '
            select 
                lm.LocationId,
                sum(p.Principal) PrincipalPaid,
                sum(p.LoanInsurance) LoanInsurancePaid,
                sum(p.HealthInsurance) HealthInsurancePaid,
                sum(p.AdministrativeFee) AdministrativeFeePaid
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                payments p on p.LoanId = l.LoanId and p.Date < :date
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                lm.LocationId';

        $this->_sql[] = '
            select 
                lo.LocationId,
                lo.Name Branch,
                count(l.LoanId) Loans,
                count(firsttime.LoanId) FirstTime,
                count(male.ClientId) Male,
                count(female.ClientId) Female,
                count(bank.LoanMasterId) Bank,
                count(individual.LoanMasterId) Individual,
                sum(l.Principal) PrincipalDisbursed,
                sum(l.LoanInsuranceMnt) LoanInsuranceDisbursed,
                sum(l.HealthInsuranceMnt) HealthInsuranceDisbursed,
                sum(l.AdministrativeFeeMnt) AdministrativeFeeDisbursed,
                sum(lwo.Principal) PrincipalWrittenOff,
                sum(lwo.Amount) AmountWrittenOff,
                count(disbursed.LoanId) Disbursed,
                count(paid.LoanId) Paid,
                count(wo.LoanId) WrittenOff,
                count(recovered.LoanId) Recovered,
                count(rural.CommunityId) Rural,
                count(urban.CommunityId) Urban,
                count(suburban.CommunityId) Suburban
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                locations lo on lo.LocationId = lm.LocationId
            left join
                clients male on male.ClientId = l.ClientId and male.Gender = "Male"
            left join
                clients female on female.ClientId = l.ClientId and female.Gender = "Female"
            left join
                loan_masters bank on bank.LoanMasterId = lm.LoanMasterId and bank.BorrowerType = "B"
            left join
                loan_masters individual on individual.LoanMasterId = lm.LoanMasterId and individual.BorrowerType = "I"
            left join
                loan_write_offs lwo on lwo.LoanId = l.LoanId
            left join
                loans disbursed on disbursed.LoanId = l.LoanId and disbursed.Status = "Disbursed"
            left join
                loans paid on paid.LoanId = l.LoanId and paid.Status = "Paid"
            left join
                loans wo on wo.LoanId = l.LoanId and wo.Status = "Written-Off"
            left join
                loans recovered on recovered.LoanId = l.LoanId and recovered.Status = "Recovered"
            left join
                communities rural on rural.CommunityId = lm.CommunityId and rural.Type = "Rural"
            left join
                communities urban on urban.CommunityId = lm.CommunityId and urban.Type = "Urban"
            left join
                communities suburban on suburban.CommunityId = lm.CommunityId and suburban.Type = "Suburban"
            left join
                loan_cycles firsttime on firsttime.LoanId = l.LoanId and firsttime.Cycle = 1
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                lo.LocationId';
    }

    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->Branch = $this->translate('Branch');
        $this->_headers->Loans = $this->translate('Loans');
        $this->_headers->PrincipalBalance = $this->translate('Principal balance');
        foreach($this->_par as $delay) {
            $lns = sprintf('Par%sLoans',$delay);
            $mnt = sprintf('Par%sMnt',$delay);
            $pct = sprintf('Par%sPct',$delay);
            $this->_headers->$lns = sprintf($this->translate('PAR %s - Loans'),$delay);
            $this->_headers->$mnt = sprintf($this->translate('PAR %s - Amount'),$delay);
            $this->_headers->$pct = sprintf($this->translate('PAR %s - Percentage'),$delay);
        }
    }
    
    protected function _process($data)
    {
        foreach($data as $row) {
            
            $data[$row->LocationId]->PrincipalBalance = 
                $row->PrincipalDisbursed - $row->PrincipalPaid;
            
            foreach($this->_par as $delay) {
                $lns = sprintf('Par%sLoans',$delay);
                $mnt = sprintf('Par%sMnt',$delay);
                $pct = sprintf('Par%sPct',$delay);
                $data[$row->LocationId]->$lns = (int) $data[$row->LocationId]->$lns;
                $data[$row->LocationId]->$mnt = (double) $data[$row->LocationId]->$mnt;
                $data[$row->LocationId]->$pct = round(100*$data[$row->LocationId]->$mnt/$data[$row->LocationId]->PrincipalBalance,2);
            }
        }
        return $data;
    }
    
    public function setParams($params) 
    {
        $this->_params = array(':date' => $params['Date']);
    }

}
